package toy.keli.picmap.model;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import toy.keli.picmap.model.types.T;

/**
 * 摘自Android Sqlite3学习： https://blog.csdn.net/w1764662543/article/details/99983192, https://cloud.tencent.com/developer/article/1394216
 */
public class db extends SQLiteOpenHelper{
    private static final String DATABASE_NAME = "kmap.db";
    protected static db instance;

    public db(Context context) {
        super(context, DATABASE_NAME, null, 1);
        instance=this;
    }

    public static void init(Context context){
        new db(context);
    }

    public void clearAll(){
        this.onUpgrade(this.getWritableDatabase(),0,0);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String sqls[]= {
                "CREATE TABLE `path` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `path` TEXT NOT NULL, `count` INTEGER DEFAULT 0, `uptime` INTEGER DEFAULT 0, `intime` INTEGER DEFAULT 0 )",
                "create unique index  path_index on path(path)",
                "CREATE TABLE `pic` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `file` TEXT NOT NULL, `x` INTEGER DEFAULT 0,`y` INTEGER DEFAULT 0, `time` TEXT DEFAULT 0, `name` TEXT NOT NULL,`intime` INTEGER DEFAULT 0 ,`pid` INTEGER )",
                "create index  pic_index on pic(file,x,y,time,name,pid)"
        };
        int i=0;
        for(;i<sqls.length;i++)
            sqLiteDatabase.execSQL(sqls[i]);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        String tabs[]={"path","pic"};
        for(i=0;i<tabs.length;i++){
            String tab=tabs[i];
            sqLiteDatabase.execSQL("drop table if  exists "+tab);
            sqLiteDatabase.execSQL("drop index if  exists "+tab+"."+tab+"_index");
        }
        this.onCreate(sqLiteDatabase);
    }

    public static SQLiteDatabase cursor(boolean canwrite){
        return canwrite? db.instance.getWritableDatabase(): db.instance.getReadableDatabase();
    }

    public static SQLiteDatabase cursor(){
        return db.cursor(false);
    }

    public static int execute(String sql, Object[] params){
        Log.d("sql",sql);
        if (params!=null && params.length > 0) {
            db.cursor(true).execSQL(sql, params);
        } else {
            db.cursor(true).execSQL(sql);
        }
//        SQLiteStatement statement = new SQLiteStatement(cursor(true), sql.toString(), params);
//        try {
//            return sql.matches("insert |INSERT ") ? statement.executeUpdateDelete() : statement.executeInsert();
//        } finally {
//            if (statement != null) statement.close();
//        }

        return 0;
    }

    public static long insert(String name,ContentValues values){
           return db.cursor(true).insert(name,"",values);
    }

    public static T query(String name,String where,String selects,String limit) {
        String select[] ={"*"};
        if(selects.length()>0) select=selects.split(",");

        String group,haveing,order;
        group=haveing=order="";
        Cursor cursor =db.cursor().query(name,select,where,null,group,haveing,order,limit);
        T rt = new T();
        if (cursor.getCount()>0){
            while(cursor.moveToNext()) {
                T row=new T();
                for(int i=0;i<cursor.getColumnCount();i++) {
                    String key=cursor.getColumnName(i);
                    String value=cursor.getString(i);
                    boolean r = row.set(key,  new T(value));
                }
                rt.push(row);
            }
        }

        return rt;
    }

    public static T query(String name,String where) {
        String limit="1000";
        return db.query(name,where,"",limit);
    }

    public static Query find(String tabname){
        return new Query(tabname);
    }
    public static class Query {
        private String tableName;
        private String[] select={"*"};
        private String where="1=1";
        private String group=null;
        private String haveing=null;
        private String order=null;
        private String limit=null;
        private Cursor cursor;
        public Query(String tabname) {
            tableName=tabname;
        }

        public void setTableName(String tableName) {
            this.tableName = tableName;
        }

        public Query() {
        }

        public Query where(String where){
            this.where=where;
            return this;
        }
        public Query limit(int n){
            this.limit=String.valueOf(n);
            return this;
        }
        public Query order(String o){
            order=o;
            return this;
        }

        public Query select(String[] s){
            select =s;
            return this;
        }

        public Query groupBy(String g){
            group=g;
            return this;
        }
        public int query(){
            cursor =db.cursor().query(tableName,select,where,null,group,haveing,order,limit);
            return cursor.getCount();
        }
        public def all(){
            return (query()>0) ?  new iterator(cursor): new def();
        }

        public def one(){
            def row=new def();
            limit(1);
            if(query()>0) {
                cursor.moveToNext();
                for(int i=0;i<cursor.getColumnCount();i++) {
                    String key=cursor.getColumnName(i);
                    String value=cursor.getString(i);
                    row.set(key,  def.valueOf(value));
                }
            }
            return row;
        }

        public int count(){
           return query();
        }

        public String getField(String name){
            def rt=one();
            return rt.get(name).toString();
        }
        public int delete(){
            String sql=String.format("DELETE from %s where %s" ,tableName,where);
            return db.execute(sql,null);
        }
    }

    static class iterator  extends def{
        private Cursor rs;
        public iterator(Cursor rs) {
            this.rs=rs;
            this.type=Types.IS_ARRAY;
        }

        protected void destroy(){
            rs.close();
        }

        @Override
        public int size() {
            return rs.getCount();
        }

        @Override
        public boolean hasNext(){
                return rs.moveToNext();
        }

        @Override
        public def next() {
            def row = new def();
            for(int i=0;i<rs.getColumnCount();i++) {
                String key=rs.getColumnName(i);
                String value=rs.getString(i);
                row.set(key,  def.valueOf(value));
            }
            return row;
        }

        public String toString(){
            for(def v : this){
                this.push(v);
            }
            return this.toString();
        }
    }
}